home *** CD-ROM | disk | FTP | other *** search
/ Power Programmierung / Power-Programmierung (Tewi)(1994).iso / magazine / dbms_mag / 9108 / bench2.aug < prev    next >
Text File  |  1991-07-19  |  4KB  |  173 lines
  1. Listing 2
  2.  
  3.  
  4. USE Benchmark
  5. go
  6. drop procedure AG_XACT4
  7. go
  8. CREATE PROCEDURE AG_XACT4 (
  9.       /*-------------------------------------------*/
  10.       /*                                           */
  11.       /*      TRANSACTION 4: Address Change        */
  12.       /*                                           */
  13.       /*-------------------------------------------*/
  14.  
  15.    @FirstName varchar(12) = NULL,
  16.    @LastName  varchar(12) = NULL,
  17.    @LineOne   varchar(32) = NULL,
  18.    @LineTwo   varchar(32) = NULL,
  19.    @City      varchar(32) = NULL,
  20.    @State     varchar(3)  = NULL,
  21.    @Zipcode   varchar(9)  = NULL)
  22.  
  23. AS BEGIN
  24.    DECLARE
  25.       @AddressID  int,
  26.       @Country   varchar(3),
  27.       @OldAddress int,
  28.       @PersonID   int,
  29.       @SubAddress int
  30.  
  31.    SELECT @Country = 'USA'
  32.  
  33.     /*-----------------------------------------*/
  34.     /* Conversion of blank strings to NULL's   */
  35.     /*-----------------------------------------*/
  36.  
  37.     IF (@FirstName = '')
  38.       SELECT @FirstName = NULL
  39.  
  40.     IF (@LastName = '')
  41.       SELECT @LastName = NULL
  42.  
  43.     IF (@LineOne = '')
  44.       SELECT @LineOne = NULL
  45.  
  46.     IF (@LineTwo = '')
  47.       SELECT @LineTwo = NULL
  48.  
  49.     IF (@City = '')
  50.       SELECT @City = NULL
  51.  
  52.     IF (@State = '')
  53.       SELECT @State = NULL
  54.  
  55.     IF (@Zipcode = '')
  56.       SELECT @Zipcode = NULL
  57.  
  58.      /*------------------------*/
  59.      /* Get a new AddressID    */
  60.      /*------------------------*/
  61.  
  62.     BEGIN TRANSACTION
  63.  
  64.       UPDATE Seqnos
  65.       SET    LastUsed = LastUsed + 1
  66.       WHERE  TableName = 'Addresses'
  67.  
  68.       SELECT @AddressID = LastUsed
  69.       FROM   Seqnos
  70.       WHERE  TableName = 'Addresses'
  71.  
  72.    COMMIT TRANSACTION
  73.  
  74.      /*------------------------*/
  75.      /*  Zipcode Verification  */
  76.      /*------------------------*/
  77.  
  78.    BEGIN TRANSACTION
  79.    IF @Zipcode IS NOT NULL
  80.       IF NOT EXISTS (SELECT *
  81.                        FROM  ZipCodes
  82.                       WHERE  Country = @Country
  83.                         AND  ZipCode = @Zipcode)
  84.       BEGIN
  85.          ROLLBACK TRANSACTION
  86.          RAISERROR 20401 "Invalid Zipcode."
  87.          RETURN
  88.       END
  89.  
  90.     /*------------------*/
  91.     /*  Insert Address  */
  92.     /*------------------*/
  93.  
  94.      INSERT INTO Addresses (ID,LineOne,LineTwo,ZipCode,Country)
  95.      VALUES (@AddressID,@LineOne,@LineTwo,@Zipcode,@Country)
  96.  
  97.       IF @@ROWCOUNT = 0
  98.          BEGIN
  99.            SELECT @AddressID = ID
  100.              FROM Addresses
  101.             WHERE LineOne = @LineOne
  102.               AND  ((LineTwo = @LineTwo) OR (LineTwo IS NULL))
  103.               AND  ZipCode = @Zipcode
  104.  
  105.            IF @@ROWCOUNT = 0
  106.              BEGIN
  107.                ROLLBACK TRANSACTION
  108.                RAISERROR 20402 "Insert Addresses failed."
  109.                RETURN
  110.              END
  111.          END
  112.  
  113.      /*-----------------------*/
  114.      /*  Look up old address  */
  115.      /*-----------------------*/
  116.  
  117.      SELECT @PersonID = ID,@OldAddress = HomeAddr
  118.      FROM   People
  119.      WHERE  LastName = @LastName AND FirstName = @FirstName
  120.  
  121.      IF @@ROWCOUNT = 0
  122.        BEGIN
  123.          ROLLBACK TRANSACTION
  124.          RAISERROR 20403 "Person not found."
  125.          RETURN
  126.        END
  127.  
  128.     /*----------*/
  129.     /* Move it. */
  130.     /*----------*/
  131.  
  132.      UPDATE People
  133.      SET   HomeAddr = @AddressID
  134.      WHERE  ID    = @PersonID
  135.  
  136.      IF @@ROWCOUNT = 0
  137.        BEGIN
  138.          ROLLBACK TRANSACTION
  139.          RAISERROR 20404 "Updating People failed."
  140.          RETURN
  141.        END
  142.  
  143.      COMMIT TRANSACTION
  144.  
  145.     /*----------------------------------*/
  146.     /* See if we can delete the old one */
  147.     /*----------------------------------*/
  148.  
  149.    IF @OldAddress IS NOT NULL
  150.      BEGIN
  151.        BEGIN TRANSACTION
  152.  
  153.          DELETE FROM Addresses
  154.          WHERE  ID = @OldAddress
  155.          AND    NOT EXISTS (SELECT *
  156.                             FROM People
  157.                             WHERE HomeAddr = @OldAddress)
  158.          AND    NOT EXISTS (SELECT *
  159.                             FROM Companies
  160.                             WHERE HQaddr = @OldAddress)
  161.          AND    NOT EXISTS (SELECT *
  162.                             FROM CompanyPeople
  163.                             WHERE WorkAddr = @OldAddress)
  164.          AND    NOT EXISTS (SELECT *
  165.                             FROM Subscriptions
  166.                             WHERE Address = @OldAddress)
  167.      COMMIT TRANSACTION
  168.    END
  169. END
  170. go
  171. GRANT ALL ON AG_XACT4 TO PUBLIC
  172. go
  173.